--Trigs & seq--
CREATE SEQUENCE loc_sequence
    START WITH 1
    INCREMENT BY 1;

CREATE TRIGGER loc_trigger
    BEFORE INSERT ON LOCATIONS
    FOR EACH ROW 
    BEGIN
        SELECT loc_sequence.nextval into :new.LOCATION_ID from DUAL;
    END;
.
RUN;

-- institutions trigger --
CREATE SEQUENCE instit1_sequence
    START WITH 1
    INCREMENT BY 1;

CREATE TRIGGER instit1_trigger
    BEFORE INSERT ON INSTITUTIONS_ATTENDED
    FOR EACH ROW
    BEGIN
        SELECT instit1_sequence.nextval into :new.INSTATTENDED_ID from DUAL;
    END;
.
RUN;

--loading stuff--
--locations--
INSERT INTO LOCATIONS (CITY, STATE, COUNTRY)
    SELECT DISTINCT HOMETOWN_CITY, HOMETOWN_STATE, HOMETOWN_COUNTRY
        FROM PUBLIC_USER_INFORMATION
    UNION
    SELECT DISTINCT CURRENT_CITY, CURRENT_STATE, CURRENT_COUNTRY
        FROM PUBLIC_USER_INFORMATION
    UNION
    SELECT DISTINCT EVENT_CITY, EVENT_STATE, EVENT_COUNTRY
        FROM PUBLIC_EVENT_INFORMATION;
/* users */
INSERT INTO USERS (USER_ID, DAY_OF_BIRTH, MONTH_OF_BIRTH, YEAR_OF_BIRTH, FIRST_NAME, LAST_NAME, GENDER, HOMETOWN_LOCATION, CURRENT_LOCATION)
    SELECT DISTINCT USER_ID, DAY_OF_BIRTH, MONTH_OF_BIRTH, YEAR_OF_BIRTH, FIRST_NAME, LAST_NAME, GENDER, 
        (SELECT LOCATION_ID FROM LOCATIONS WHERE 
            LOCATIONS.CITY = PUBLIC_USER_INFORMATION.HOMETOWN_CITY AND 
            LOCATIONS.STATE = PUBLIC_USER_INFORMATION.HOMETOWN_STATE AND 
            LOCATIONS.COUNTRY = PUBLIC_USER_INFORMATION.HOMETOWN_COUNTRY),
        (SELECT LOCATION_ID FROM LOCATIONS WHERE 
            LOCATIONS.CITY = PUBLIC_USER_INFORMATION.CURRENT_CITY AND 
            LOCATIONS.STATE = PUBLIC_USER_INFORMATION.CURRENT_STATE AND 
            LOCATIONS.COUNTRY = PUBLIC_USER_INFORMATION.CURRENT_COUNTRY)
        FROM PUBLIC_USER_INFORMATION;


/* events*/
INSERT INTO EVENTS (EVENT_ID, EVENT_CREATOR_ID, EVENT_NAME, EVENT_TAGLINE, EVENT_DESCRIPTION, EVENT_HOST, EVENT_TYPE, EVENT_SUBTYPE, EVENT_LOCATION, EVENT_START_TIME, EVENT_END_TIME)
    SELECT DISTINCT EVENT_ID, EVENT_CREATOR_ID, EVENT_NAME, EVENT_TAGLINE, EVENT_DESCRIPTION, EVENT_HOST, EVENT_TYPE, EVENT_SUBTYPE,
        (SELECT LOCATION_ID FROM LOCATIONS WHERE 
            LOCATIONS.CITY = PUBLIC_EVENT_INFORMATION.EVENT_CITY AND 
            LOCATIONS.STATE = PUBLIC_EVENT_INFORMATION.EVENT_STATE AND 
            LOCATIONS.COUNTRY = PUBLIC_EVENT_INFORMATION.EVENT_COUNTRY), EVENT_START_TIME, EVENT_END_TIME
        FROM PUBLIC_EVENT_INFORMATION;

/* institutions attended*/
INSERT INTO INSTITUTIONS_ATTENDED (INSTITUTION_NAME, USER_ID, PROGRAM_YEAR, PROGRAM_CONC, PROGRAM_DEGREE)
    SELECT DISTINCT INSTITUTION_NAME, USER_ID, PROGRAM_YEAR, PROGRAM_CONCENTRATION, PROGRAM_DEGREE
        FROM PUBLIC_USER_INFORMATION;

/* friends NOTE NOT PUTTING IT IN TWICE*/
INSERT INTO FRIENDS (USER1_ID, USER2_ID)
    SELECT DISTINCT USER1_ID, USER2_ID FROM PUBLIC_ARE_FRIENDS
        WHERE NOT EXISTS (SELECT USER1_ID, USER2_ID FROM FRIENDS WHERE
                                FRIENDS.USER1_ID = PUBLIC_ARE_FRIENDS.USER2_ID AND
                                FRIENDS.USER2_ID = PUBLIC_ARE_FRIENDS.USER1_ID);
/* albums 
    not sure where cover photo should be, also get owner name
*/
INSERT INTO ALBUMS (ALBUM_ID, OWNER_ID, COVER_PHOTO, ALBUM_NAME,  ALBUM_CREATED_TIME,  ALBUM_MODIFIED_TIME, ALBUM_LINK, ALBUM_VISIBILITY)
    SELECT DISTINCT ALBUM_ID, OWNER_ID, COVER_PHOTO_ID, ALBUM_NAME,  ALBUM_CREATED_TIME, ALBUM_MODIFIED_TIME, ALBUM_LINK, ALBUM_VISIBILITY
        FROM PUBLIC_PHOTO_INFORMATION;

/* photos */
INSERT INTO PHOTOS (PHOTO_ID, PHOTO_CAPTION,  PHOTO_CREATED_TIME, PHOTO_MODIFIED_TIME, PHOTO_LINK, ALBUM_ID)
    SELECT DISTINCT PHOTO_ID, PHOTO_CAPTION, PHOTO_CREATED_TIME, PHOTO_MODIFIED_TIME, PHOTO_LINK, ALBUM_ID
        FROM PUBLIC_PHOTO_INFORMATION;

COMMIT;

/* tags */
INSERT INTO TAGS (PHOTO_ID, TAG_SUBJECT_ID, TAG_CREATED_TIME, TAG_X_COORDINATE,  TAG_Y_COORDINATE)
    SELECT DISTINCT PHOTO_ID, TAG_SUBJECT_ID, TAG_CREATED_TIME, TAG_X_COORDINATE, TAG_Y_COORDINATE
        FROM PUBLIC_TAG_INFORMATION; 

